package core;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import util.DB;

public class UserDAO {

    private static User makeUser(ResultSet rs) throws Exception {
        User obj = new User();
        obj.setId(rs.getInt("Id"));
        obj.setLoginId(rs.getString("loginId"));
        obj.setPasswd(rs.getString("passwd"));
        obj.setName(rs.getString("name"));
        obj.setEmail(rs.getString("email"));
        obj.setEnabled(rs.getBoolean("enabled"));
        return obj;
    }

    public static User selectByLoginId(String loginId) throws Exception {
        String sql = "SELECT * FROM [User] WHERE [loginId] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt= con.prepareStatement(sql)) {
            stmt.setString(1, loginId);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? makeUser(rs) : null;
            }
        }
    }

    public static User selectById(int id) throws Exception {
        String sql = "SELECT * FROM [User] WHERE [Id] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? makeUser(rs) : null;
            }
        }
    }

    public static ArrayList<User> selectByUserGroupId(int userGroupId) throws Exception {
        String sql = "EXEC userSelectByUserGroupId ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, userGroupId);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<User> list = new ArrayList<User>();
                while (rs.next()) list.add(makeUser(rs));
                return list;
            }
        }
    }

    public static ArrayList<User> selectPage(int currentPage, int pageSize, int order, int srchType, String srchText, int category) throws Exception {
        String sql = "EXEC userSelectPage ?, ?, ?, ?, ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, currentPage);
            stmt.setInt(2, pageSize);
            stmt.setInt(3, order);
            stmt.setInt(4, srchType);
            stmt.setString(5, srchText);
            stmt.setInt(6, category);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<User> list = new ArrayList<User>();
                while (rs.next()) list.add(makeUser(rs));
                return list;
            }
        }
    }

    public static int getRecordCount(int srchType, String srchText, int category) throws Exception {
        String sql = "EXEC userRecordCount ?, ?, ?";
        try (Connection con  = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, srchType);
            stmt.setString(2, srchText);
            stmt.setInt(3, category);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    public static void insert(String loginId, String passwd, String name, String email) throws Exception {
        String sql = "INSERT [User] ([loginId], [passwd], [name], [email], [enabled]) VALUES (?, ?, ?, ?, 1)";
        try (Connection con  = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, loginId);
            stmt.setString(2, passwd);
            stmt.setString(3, name);
            stmt.setString(4, email);
            stmt.executeUpdate();
        }
    }

    public static void updateInfo(int id, String loginId, String name, String email, boolean enabled) throws Exception {
        String sql = "UPDATE [User] SET [loginId] = ?, [name] = ?, [email] = ?, [enabled] = ? WHERE [Id] = ?";
        try (Connection con  = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, loginId);
            stmt.setString(2, name);
            stmt.setString(3, email);
            stmt.setBoolean(4, enabled);
            stmt.setInt(5, id);
            stmt.executeUpdate();
        }
    }

    public static void updatePasswd(int id, String passwd) throws Exception {
        String sql = "UPDATE [User] SET [passwd] = ? WHERE [id] = ?";
        try (Connection con  = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, passwd);
            stmt.setInt(2, id);
            stmt.executeUpdate();
        }
    }

}
